{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Self join\n",
    "\n",
    "## Edinburgh Buses\n",
    "[Details of the database](https://sqlzoo.net/wiki/Edinburgh_Buses.) Looking at the data\n",
    "\n",
    "```\n",
    "stops(id, name)\n",
    "route(num, company, pos, stop)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "sc = (SparkSession.builder.appName('app09') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "How many **stops** are in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "stops = sc.read.table('sqlzoo.stops')\n",
    "route = sc.read.table('sqlzoo.route')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(id)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>246</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(id)\n",
       "0        246"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stops.agg({'id': 'count'}).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Find the **id** value for the stop 'Craiglockhart'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0  53"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stops.filter(stops['name']=='Craiglockhart').select('id').toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Give the **id** and the **name** for the **stops** on the '4' 'LRT' service."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19</td>\n",
       "      <td>Bingham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53</td>\n",
       "      <td>Craiglockhart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>85</td>\n",
       "      <td>Fairmilehead</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>115</td>\n",
       "      <td>Haymarket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>117</td>\n",
       "      <td>Hillend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>149</td>\n",
       "      <td>London Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>177</td>\n",
       "      <td>Northfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>179</td>\n",
       "      <td>Oxgangs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>194</td>\n",
       "      <td>Princes Street</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id            name\n",
       "0   19         Bingham\n",
       "1   53   Craiglockhart\n",
       "2   85    Fairmilehead\n",
       "3  115       Haymarket\n",
       "4  117         Hillend\n",
       "5  149     London Road\n",
       "6  177      Northfield\n",
       "7  179         Oxgangs\n",
       "8  194  Princes Street"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql.functions import *\n",
    "(stops.join(route, stops['id']==route['stop'], how='left')\n",
    " .filter((col('num')=='4') & (col('company')=='LRT'))\n",
    " .select('id', 'name').toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Routes and stops\n",
    "\n",
    "The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "      <th>count(stop)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  company num  count(stop)\n",
       "0     LRT  45            2\n",
       "1     LRT   4            2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.filter(route['stop'].isin([149, 53]))\n",
    "    .groupBy('company', 'num')\n",
    "    .agg({'stop': 'count'})\n",
    "    .filter(\"count(stop)==2\")\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "      <th>stop</th>\n",
       "      <th>stop2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "      <td>53</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "      <td>53</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  company num  stop  stop2\n",
       "0     LRT   4    53    149\n",
       "1     LRT  45    53    149"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.join(route\n",
    "            .withColumnRenamed('pos', 'pos2')\n",
    "            .withColumnRenamed('stop', 'stop2'), \n",
    "            ['company', 'num'], how='inner')\n",
    "    .filter((col('stop')==53) & (col('stop2')==149))\n",
    "    .select('company', 'num', 'stop', 'stop2')\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "The query shown is similar to the previous one, however by joining two copies of the **stops** table we can refer to **stops** by **name** rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "      <th>name</th>\n",
       "      <th>name2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>London Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>London Road</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  company num           name        name2\n",
       "0     LRT   4  Craiglockhart  London Road\n",
       "1     LRT  45  Craiglockhart  London Road"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.join(route\n",
    "            .withColumnRenamed('pos', 'pos2')\n",
    "            .withColumnRenamed('stop', 'stop2'), \n",
    "            ['company', 'num'])\n",
    "    .join(stops, col('stop')==stops['id'])\n",
    "    .join(stops\n",
    "          .withColumnRenamed('id', 'id2')\n",
    "          .withColumnRenamed('name', 'name2'), \n",
    "          col('stop2')==col('id2'))\n",
    "    .filter((col('name')==\"Craiglockhart\") & (col('name2')==\"London Road\"))\n",
    "    .select('company', 'num', 'name', 'name2')\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. [Using a self join](https://sqlzoo.net/wiki/Using_a_self_join)\n",
    "\n",
    "Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LRT</td>\n",
       "      <td>2A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LRT</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LRT</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SMT</td>\n",
       "      <td>C5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LRT</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LRT</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  company num\n",
       "0     LRT  2A\n",
       "1     LRT   2\n",
       "2     LRT  25\n",
       "3     SMT  C5\n",
       "4     LRT  12\n",
       "5     LRT  22"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.join(route\n",
    "            .withColumnRenamed('pos', 'pos2')\n",
    "            .withColumnRenamed('stop', 'stop2'),\n",
    "            ['company', 'num'])\n",
    "    .filter((col('stop')==115) & (col('stop2')==137))\n",
    "    .select('company', 'num')\n",
    "    .dropDuplicates()\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  company num\n",
       "0     LRT  10\n",
       "1     LRT  27\n",
       "2     LRT  45\n",
       "3     LRT  47"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.join(route\n",
    "            .withColumnRenamed('pos', 'pos2')\n",
    "            .withColumnRenamed('stop', 'stop2'),\n",
    "            ['company', 'num'])\n",
    "    .join(stops, col('stop')==col('id'))\n",
    "    .join(stops\n",
    "          .withColumnRenamed('id', 'id2')\n",
    "          .withColumnRenamed('name', 'name2'), \n",
    "          col('stop2')==col('id2'))\n",
    "    .filter((col('name')==\"Craiglockhart\") & \n",
    "            (col('name2')==\"Tollcross\"))\n",
    "    .select('company', 'num')\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "Give a distinct list of the **stops** which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name2</th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tollcross</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Duddingston</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Balerno Church</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hillend</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Tollcross</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tollcross</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Riccarton Campus</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Princes Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Oxgangs</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Silverknowes</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Balerno</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Cockburn Crescent</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>London Road</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Fairmilehead</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>London Road</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Oxgangs</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Crewe Toll</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Hanover Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Colinton</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Canonmills</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Tollcross</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brunstane</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Newhaven</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Leith</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Colinton</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Princes Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Northfield</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Haymarket</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Silverknowes</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Bingham</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Northfield</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Hanover Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Torphin</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Leith Walk</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Currie</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Currie</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Colinton</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Hunters Tryst</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Hanover Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Canonmills</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Muirhouse</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name2 company num\n",
       "0           Tollcross     LRT  27\n",
       "1         Duddingston     LRT  45\n",
       "2      Balerno Church     LRT  47\n",
       "3       Craiglockhart     LRT  10\n",
       "4             Hillend     LRT   4\n",
       "5           Tollcross     LRT  47\n",
       "6           Tollcross     LRT  45\n",
       "7    Riccarton Campus     LRT  45\n",
       "8      Princes Street     LRT   4\n",
       "9             Oxgangs     LRT  27\n",
       "10       Silverknowes     LRT  10\n",
       "11            Balerno     LRT  47\n",
       "12  Cockburn Crescent     LRT  47\n",
       "13        London Road     LRT   4\n",
       "14       Fairmilehead     LRT   4\n",
       "15      Craiglockhart     LRT  45\n",
       "16        London Road     LRT  45\n",
       "17            Oxgangs     LRT   4\n",
       "18         Crewe Toll     LRT  27\n",
       "19     Hanover Street     LRT  47\n",
       "20           Colinton     LRT  45\n",
       "21         Canonmills     LRT  47\n",
       "22          Tollcross     LRT  10\n",
       "23          Brunstane     LRT  45\n",
       "24      Craiglockhart     LRT  27\n",
       "25           Newhaven     LRT  10\n",
       "26              Leith     LRT  10\n",
       "27           Colinton     LRT  47\n",
       "28      Craiglockhart     LRT   4\n",
       "29     Princes Street     LRT  10\n",
       "30         Northfield     LRT  45\n",
       "31          Haymarket     LRT   4\n",
       "32       Silverknowes     LRT  27\n",
       "33            Bingham     LRT   4\n",
       "34         Northfield     LRT   4\n",
       "35     Hanover Street     LRT  45\n",
       "36            Torphin     LRT  10\n",
       "37         Leith Walk     LRT  10\n",
       "38             Currie     LRT  45\n",
       "39             Currie     LRT  47\n",
       "40           Colinton     LRT  10\n",
       "41      Craiglockhart     LRT  47\n",
       "42      Hunters Tryst     LRT  27\n",
       "43     Hanover Street     LRT  27\n",
       "44         Canonmills     LRT  27\n",
       "45          Muirhouse     LRT  10"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.join(route\n",
    "            .withColumnRenamed('pos', 'pos2')\n",
    "            .withColumnRenamed('stop', 'stop2'),\n",
    "            ['company', 'num'])\n",
    "    .join(stops, col('stop')==col('id'))\n",
    "    .join(stops\n",
    "          .withColumnRenamed('id', 'id2')\n",
    "          .withColumnRenamed('name', 'name2'), \n",
    "          col('stop2')==col('id2'))\n",
    "    .filter((col('name')==\"Craiglockhart\") & (col('company')==\"LRT\"))\n",
    "    .select('name2', 'company', 'num')\n",
    "    .dropDuplicates()\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Find the routes involving two buses that can go from **Craiglockhart** to **Lochend**.\n",
    "Show the bus no. and company for the first bus, the name of the stop for the transfer,\n",
    "and the bus no. and company for the second bus.\n",
    "\n",
    "> _Hint_    \n",
    "> Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>company</th>\n",
       "      <th>name2</th>\n",
       "      <th>num2</th>\n",
       "      <th>company2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Duddingston</td>\n",
       "      <td>46A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Crewe Toll</td>\n",
       "      <td>20</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Haymarket</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Duddingston</td>\n",
       "      <td>42</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>47</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Canonmills</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>27</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Canonmills</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Haymarket</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>47</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Canonmills</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>27</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Canonmills</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Riccarton Campus</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>42</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>42</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Princes Street</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Princes Street</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Leith</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>87</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>87</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Leith</td>\n",
       "      <td>87</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>87A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>87A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Leith</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Leith</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Princes Street</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Princes Street</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>20</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>20</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>46A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>46A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num company             name2 num2 company2\n",
       "0   45     LRT       Duddingston  46A      LRT\n",
       "1   27     LRT        Crewe Toll   20      LRT\n",
       "2    4     LRT         Haymarket   C5      SMT\n",
       "3   45     LRT       Duddingston   42      LRT\n",
       "4   47     LRT        Canonmills   35      LRT\n",
       "5   27     LRT        Canonmills   35      LRT\n",
       "6    4     LRT         Haymarket   65      LRT\n",
       "7   47     LRT        Canonmills   34      LRT\n",
       "8   27     LRT        Canonmills   34      LRT\n",
       "9   45     LRT  Riccarton Campus   65      LRT\n",
       "10   4     LRT       London Road   42      LRT\n",
       "11  45     LRT       London Road   42      LRT\n",
       "12  10     LRT    Princes Street   65      LRT\n",
       "13   4     LRT    Princes Street   65      LRT\n",
       "14  10     LRT             Leith   35      LRT\n",
       "15   4     LRT       London Road   87      LRT\n",
       "16  45     LRT       London Road   87      LRT\n",
       "17   4     LRT       London Road   35      LRT\n",
       "18  45     LRT       London Road   35      LRT\n",
       "19  10     LRT             Leith   87      LRT\n",
       "20   4     LRT       London Road  87A      LRT\n",
       "21  45     LRT       London Road  87A      LRT\n",
       "22   4     LRT       London Road   C5      SMT\n",
       "23  45     LRT       London Road   C5      SMT\n",
       "24   4     LRT       London Road   34      LRT\n",
       "25  45     LRT       London Road   34      LRT\n",
       "26   4     LRT       London Road   65      LRT\n",
       "27  45     LRT       London Road   65      LRT\n",
       "28  10     LRT             Leith   34      LRT\n",
       "29  10     LRT             Leith   C5      SMT\n",
       "30  10     LRT    Princes Street   C5      SMT\n",
       "31   4     LRT    Princes Street   C5      SMT\n",
       "32   4     LRT       London Road   20      LRT\n",
       "33  45     LRT       London Road   20      LRT\n",
       "34   4     LRT       London Road  46A      LRT\n",
       "35  45     LRT       London Road  46A      LRT"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bus1 = (route.join(route\n",
    "            .withColumnRenamed('pos', 'pos2')\n",
    "            .withColumnRenamed('stop', 'stop2'),\n",
    "            ['company', 'num'])\n",
    "    .join(stops, col('stop')==col('id'))\n",
    "    .join(stops\n",
    "          .withColumnRenamed('id', 'id2')\n",
    "          .withColumnRenamed('name', 'name2'), \n",
    "          col('stop2')==col('id2'))\n",
    "    .filter(col('name')==\"Craiglockhart\")\n",
    "    .select('name2', 'company', 'num', 'stop2')\n",
    "    .dropDuplicates())\n",
    "bus2 = (route.join(route\n",
    "            .withColumnRenamed('pos', 'pos2')\n",
    "            .withColumnRenamed('stop', 'stop2'),\n",
    "            ['company', 'num'])\n",
    "    .join(stops, col('stop')==col('id'))\n",
    "    .join(stops\n",
    "          .withColumnRenamed('id', 'id2')\n",
    "          .withColumnRenamed('name', 'name2'), \n",
    "          col('stop2')==col('id2'))\n",
    "    .filter(col('name2')==\"Lochend\")\n",
    "    .select('stop', 'company', 'num')\n",
    "    .dropDuplicates())\n",
    "(bus1.join(bus2\n",
    "           .withColumnRenamed('company', 'company2')\n",
    "           .withColumnRenamed('num', 'num2'), \n",
    "           bus1['stop2']==bus2['stop'])\n",
    "    .select('num', 'company', 'name2', 'num2', 'company2')\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
